/**
 * 登录验证逻辑中，为了体现SQL拼接中，容易出现的 ' 单引号问题，采用了Statement
 * 故效率不高，同时存在SQL注入问题，如：输入，用户名：，密码：可登录成功。
 */
package com.jahentao.unitTest.case07;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * UserDAO的实现
 * <p>SystemLoginModule已说明，数据库中密码是明文，未处理的，所以有如下查询操作
 * @author jahentao
 * @date 2018-4-25
 * @see com.jahentao.unitTest.case07.SystemLoginModule
 */
public class JdbcUserDAO implements UserDAO {
	
	public DataSource getDataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		String url = new File("db/test").getAbsolutePath();
		dataSource.setUrl("jdbc:h2:"+url);
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Override
	public User findUser(String username, String password) {
		// 拼接出SQL查询语句
		String sql = "SELECT * FROM USER WHERE USERNAME = '"
				+ username.trim() + "' AND PASSWORD = '" + password.trim() + "'";
		
		
		Connection conn = null;
		
		try {
			conn = getDataSource().getConnection();
			Statement statement = conn.createStatement();
			
			ResultSet rs = statement.executeQuery(sql);
			
			User user = null;
			if (rs.next()) {
				user = new User(
					rs.getLong("ID"),
					rs.getString("USERNAME"), 
					rs.getString("PASSWORD")
				);
			}
			rs.close();
			statement.close();
			return user;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
				conn.close();
				} catch (SQLException e) {}
			}
		}
	}

}
